package yki.paperplane.server.db.planeoperator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Statement;

import yki.paperplane.server.db.DBOperator;
import yki.paperplane.server.model.Plane;

public class RetriveNewPlaneOperator extends DBOperator{

	int userid;
	double longtitude;
	double latitude;
	
	public RetriveNewPlaneOperator( int userid, double longtitude, double latitude ){
		this.userid = userid;
		this.longtitude = longtitude;
		this.latitude = latitude;
	}
	@Override
	public Plane execute(Connection connection) throws SQLException{
		PreparedStatement s1 = connection.prepareStatement("SET @lon=" + longtitude);
		s1.execute();
		PreparedStatement s2 = connection.prepareStatement("SET @lat=" + latitude);
		s2.execute();
		PreparedStatement statement = connection.prepareStatement(SQL_RANDOMRETRIVEAPLANE);
		statement.setInt(1, userid);
		ResultSet resultSet = statement.executeQuery();
		if( resultSet.first() ){
			Plane plane = new Plane();
			setupPlaneInfo(plane, resultSet);
			statement = connection.prepareStatement( SQL_UPDATEPLANERECIVER);
			statement.setInt(1,  userid);
			statement.setInt(2, plane.getLetterID());
			statement.executeUpdate();

			UpdatePlaneStateOperator updateStateOperator = new UpdatePlaneStateOperator(
					plane.getLetterID(), 1);
			updateStateOperator.execute(connection);
			return plane;
		}else {
			return null;
		}
			
	}
	
	private void setupPlaneInfo( Plane plane, ResultSet resultSet ) throws SQLException{
		plane.setLetterID( resultSet.getInt( LETTER_ID ) );
		plane.setSenderID( resultSet.getInt( SENDER_ID) );
		plane.setMessage( resultSet.getString( MESSAGE));
		plane.setLongtitude( resultSet.getDouble( LONGITUDE));
		plane.setLatitude( resultSet.getDouble( LATITUDE));
		plane.setRadius( resultSet.getInt( RADIUS));
		plane.setPaperID( resultSet.getInt( PAPER_ID));
		plane.setDate( resultSet.getDate( DATE));
		plane.setValidDate( resultSet.getDate( VALID_PERIOD));
	}

}
